Imports OutlookStyleControls
Imports System.ComponentModel
Imports System.Collections
Imports ADOConnection


Public Class frmKiemKe
    Private m_nhakho As String = ""
    Private m_loaikiemke As Boolean
    Private m_loaikho As String = ""


    Public Sub New(ByVal nhakho As String, ByVal loaikho As String, ByVal loaikiemke As Boolean)
        'loaikiemke  true: thang 1   false : thang 7
        ' This call is required by the Windows Form Designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        m_nhakho = nhakho
        m_loaikho = loaikho
        m_loaikiemke = loaikiemke
        If (loaikiemke) Then
            lblKiemKe.Text += " ĐẦU KỲ " + Chr(10) + "NHÀ KHO " + nhakho + "- LOẠI KHO " + loaikho
        Else
            lblKiemKe.Text += " THÁNG 7 " + Chr(10) + " NHÀ KHO " + nhakho + "- LOẠI KHO " + loaikho
        End If
    End Sub

    Private Sub frmKiemKe_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        KhoiTaodtgChiTietKiemKe(m_nhakho, m_loaikho, m_loaikiemke)
    End Sub

#Region "btn btnCapNhatKiemKe"
    Private Sub GhiSoLieuKiemKe(ByVal flag As Boolean)
        btnGhi.Visible = flag
        btnHuy.Visible = flag
        btnCapNhat.Visible = Not flag
        With gridKiemKe.RootTable
            If (Not flag) Then
                .Columns.Item("hkk1").EditType = Janus.Windows.GridEX.EditType.NoEdit
                .Columns.Item("slkk11").EditType = Janus.Windows.GridEX.EditType.NoEdit
                .Columns.Item("hkk7").EditType = Janus.Windows.GridEX.EditType.NoEdit
                .Columns.Item("slkk17").EditType = Janus.Windows.GridEX.EditType.NoEdit
                .Columns.Item("sltl11").EditType = Janus.Windows.GridEX.EditType.NoEdit
                .Columns.Item("sltl17").EditType = Janus.Windows.GridEX.EditType.NoEdit
            Else
                .Columns.Item("hkk1").EditType = Janus.Windows.GridEX.EditType.TextBox
                .Columns.Item("slkk11").EditType = Janus.Windows.GridEX.EditType.TextBox
                .Columns.Item("hkk7").EditType = Janus.Windows.GridEX.EditType.TextBox
                .Columns.Item("slkk17").EditType = Janus.Windows.GridEX.EditType.TextBox
                .Columns.Item("sltl11").EditType = Janus.Windows.GridEX.EditType.TextBox
                .Columns.Item("sltl17").EditType = Janus.Windows.GridEX.EditType.TextBox
            End If
        End With
    End Sub

    Private Sub btnCapNhat_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCapNhat.Click
        GhiSoLieuKiemKe(True)
        'get default value for col slkk from slss
        Dim loai_kiemke As Boolean
        Dim flag_getdefaultvalue As Boolean = True
        'Dim i As Integer = lbxKiemKe.SelectedIndex()
        If (Not m_loaikiemke) Then
            'kiem ke giua ki
            'thuc hien kiem tra
            For Each row As Janus.Windows.GridEX.GridEXRow In gridKiemKe.GetRows
                For Each r As Janus.Windows.GridEX.GridEXRow In row.GetChildRows()
                    If (r.Cells("slkk17").Value <> 0) Then
                        flag_getdefaultvalue = False
                        Exit For
                    End If
                Next
            Next
            loai_kiemke = False
            If (flag_getdefaultvalue) Then
                For Each row As Janus.Windows.GridEX.GridEXRow In gridKiemKe.GetRows
                    For Each r As Janus.Windows.GridEX.GridEXRow In row.GetChildRows()
                        r.BeginEdit()

                        r.Cells("slkk17").Value = r.Cells("slss17").Value
                        r.Cells("hkk7").Value = r.Cells("h").Value
                        'so luong thanh ly
                        'trang bi H>3 -> Thanh Ly
                        'thuoc, dung cu H>2 -> Thanh Ly
                        If (r.Cells("maloaithuoc").Value.ToString.Substring(0, 3) = "Y01") Then
                            'trang bi
                            If (CInt(r.Cells("h").Value) > 3) Then
                                r.Cells("sltl17").Value = r.Cells("slss17").Value
                            End If
                        Else 'khac trang bi
                            If (CInt(r.Cells("h").Value) > 2) Then
                                r.Cells("sltl17").Value = r.Cells("slss17").Value
                            End If
                        End If
                        r.EndEdit()
                    Next
                Next
            End If
        Else 'kiem ke dau ki
            'thuc hien kiem tra
            For Each row As Janus.Windows.GridEX.GridEXRow In gridKiemKe.GetRows
                For Each r As Janus.Windows.GridEX.GridEXRow In row.GetChildRows()
                    If (r.Cells("slkk11").Value <> 0) Then
                        flag_getdefaultvalue = False
                        Exit For
                    End If
                Next
            Next
            loai_kiemke = True
            If (flag_getdefaultvalue) Then
                For Each row As Janus.Windows.GridEX.GridEXRow In gridKiemKe.GetRows
                    For Each r As Janus.Windows.GridEX.GridEXRow In row.GetChildRows()
                        'beginEdit
                        r.BeginEdit()

                        r.Cells("slkk11").Value = r.Cells("slss11").Value
                        r.Cells("hkk1").Value = r.Cells("h").Value
                        'so luong thanh ly
                        'trang bi H>3 -> Thanh Ly
                        'thuoc, dung cu H>2 -> Thanh Ly
                        If (r.Cells("maloaithuoc").Value.ToString.Substring(0, 3) = "Y01") Then
                            'trang bi
                            If (CInt(r.Cells("h").Value) > 3) Then
                                r.Cells("sltl11").Value = r.Cells("slss11").Value
                            End If
                        Else 'khac trang bi
                            If (CInt(r.Cells("h").Value) > 2) Then
                                r.Cells("sltl11").Value = r.Cells("slss11").Value
                            End If
                        End If
                        r.EndEdit()
                    Next
                Next
            End If
        End If
    End Sub

    Private Sub btnGhi_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGhi.Click
        For Each row As Janus.Windows.GridEX.GridEXRow In gridKiemKe.GetRows
            For Each r As Janus.Windows.GridEX.GridEXRow In row.GetChildRows()
                Dim objItem As New CMatHang
                'chi sua sltt, hkk, lydo
                'co the sua slss, dieuchinh lam sau neu yeu cau
                objItem.MatHang_ID = r.Cells("mathang_id").Value
                objItem.HKK1 = IIf(r.Cells("hkk1").Value Is DBNull.Value, Nothing, r.Cells("hkk1").Value)
                objItem.SLSS11 = r.Cells("slss11").Value
                objItem.SLKK11 = r.Cells("slkk11").Value
                objItem.HKK7 = IIf(r.Cells("hkk7").Value Is DBNull.Value, Nothing, r.Cells("hkk7").Value)
                objItem.SLSS17 = r.Cells("slss17").Value
                objItem.SLKK17 = r.Cells("slkk17").Value
                objItem.SLTL11 = IIf(r.Cells("sltl11").Value Is DBNull.Value, 0, r.Cells("sltl11").Value)
                objItem.SLTL17 = IIf(r.Cells("sltl17").Value Is DBNull.Value, 0, r.Cells("sltl17").Value)
                CMatHangs.UpdateKiemKe(objItem)
            Next
        Next
        GhiSoLieuKiemKe(False)
    End Sub

    Private Sub btnHuy_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHuy.Click
        'refesh lai du lieu
        KhoiTaodtgChiTietKiemKe(m_nhakho, m_loaikho, m_loaikiemke)
        GhiSoLieuKiemKe(False)
    End Sub
#End Region

#Region "dtgChiTietKiemKe"
    Public Sub KhoiTaodtgChiTietKiemKe(ByVal ma_nhakho As String, ByVal ma_loaikho As String, ByVal loai_kiemke As Boolean)
        'loai : kiem ke dau ki: true, giua ki: false
        Dim flag As Boolean = False
        Dim strsql As String
        strsql = "select maloaithuoc,mathang_id,(ma + code) as ma_mathang,ma,code,ten,lo,hansd,gle,dvt,h,slss11,slkk11,hkk1,slss17,slkk17,hkk7,sltl11,sltl17" & _
            " from viewChiTietKiemKe "
        If (m_nhakho <> "") Then
            strsql &= " where kho = '" & ma_nhakho & "'"
            If (m_loaikho <> "") Then
                strsql &= " and lk = '" & ma_loaikho & "'"
            End If
        Else
            If (m_loaikho <> "") Then
                strsql &= " where lk = '" & ma_loaikho & "'"
            Else
                flag = True
                If (loai_kiemke) Then
                    'dau ki
                    strsql &= " where slss11 <> 0 and slss11 is not NULL"
                Else 'giua ki
                    strsql &= " where slss17 <> 0 and slss17 is not NULL"
                End If
            End If
        End If
        'loai bo nhung lo hang nao moi nhap sau dot kiem ke
        If (Not flag) Then
            If (loai_kiemke) Then
                'dau ki
                strsql &= " and slss11 <> 0 and slss11 is not NULL"
            Else 'giua ki
                strsql &= " and slss17 <> 0 and slss17 is not NULL"
            End If
        End If
        strsql &= " order by maloaithuoc,ma"
        '
        Dim ds As DataSet
        ds = BaseDB.ExecSql_DataSet(strsql)
        gridKiemKe.DataSource = ds.Tables(0)
        gridKiemKe.RetrieveStructure()
        gridKiemKe.RootTable.Groups.Add(New Janus.Windows.GridEX.GridEXGroup(gridKiemKe.RootTable.Columns("maloaithuoc")))
        gridKiemKe.RootTable.SortKeys.Add(New Janus.Windows.GridEX.GridEXSortKey(gridKiemKe.RootTable.Columns("ma"), SortOrder.Ascending))
        gridKiemKe.GroupByBoxVisible() = False
        FormatGridStyleChiTietKiemKe(loai_kiemke)
    End Sub

    Public Sub FormatGridStyleChiTietKiemKe(ByVal loai_kiemke As Boolean)
        With gridKiemKe.RootTable
            With .Columns.Item("maloaithuoc")
                .Caption = "Nhóm"
                .EditType = Janus.Windows.GridEX.EditType.NoEdit
                .Visible = False
            End With
            With .Columns.Item("ma")
                .Caption = "Mã"
                .EditType = Janus.Windows.GridEX.EditType.NoEdit
                .Visible = False
            End With
            With .Columns.Item("ma_mathang")
                .Caption = "Mã"
                .EditType = Janus.Windows.GridEX.EditType.NoEdit
                .Visible = False
            End With
            With .Columns.Item("mathang_id")
                .Caption = ""
                .EditType = Janus.Windows.GridEX.EditType.NoEdit
                .Visible = False
            End With
            With .Columns.Item("code")
                .Caption = "Code"
                .EditType = Janus.Windows.GridEX.EditType.NoEdit
                .Visible = False
            End With
            With .Columns.Item("ten")
                .Caption = "Tên hàng"
                .EditType = Janus.Windows.GridEX.EditType.NoEdit
                .Width = 210
            End With
            With .Columns.Item("lo")
                .Caption = "Lô hàng"
                .EditType = Janus.Windows.GridEX.EditType.NoEdit
                .Width = 100
            End With
            With .Columns.Item("hansd")
                .Caption = "HSD"
                .TextAlignment = Janus.Windows.GridEX.TextAlignment.Center
                .EditType = Janus.Windows.GridEX.EditType.NoEdit
                .Width = 70
            End With
            With .Columns.Item("dvt")
                .Caption = "DVT"
                .TextAlignment = Janus.Windows.GridEX.TextAlignment.Center
                .EditType = Janus.Windows.GridEX.EditType.NoEdit
                .Width = 50
            End With
            With .Columns.Item("h")
                .Caption = "H"
                .TextAlignment = Janus.Windows.GridEX.TextAlignment.Center
                .EditType = Janus.Windows.GridEX.EditType.NoEdit
                .Width = 30
            End With
            With .Columns.Item("gle")
                .Caption = "Đơn giá"
                .FormatString = "#,###.##"
                .TextAlignment = Janus.Windows.GridEX.TextAlignment.Far
                .EditType = Janus.Windows.GridEX.EditType.NoEdit
                .Width = 80
            End With
            'chinh sua sltt sau khi kiem ke
            If (loai_kiemke) Then
                'kiem ke dau ki
                With .Columns.Item("hkk1")
                    .Caption = "HKK"
                    .EditType = Janus.Windows.GridEX.EditType.NoEdit
                    .Width = 30
                End With
                With .Columns.Item("slss11")
                    .Caption = "SLSS"
                    .FormatString = "#,###.##"
                    .TextAlignment = Janus.Windows.GridEX.TextAlignment.Far
                    .EditType = Janus.Windows.GridEX.EditType.NoEdit
                    .Width = 60
                End With
                With .Columns.Item("slkk11")
                    .Caption = "SLKK"
                    .FormatString = "#,###.##"
                    .TextAlignment = Janus.Windows.GridEX.TextAlignment.Far
                    .EditType = Janus.Windows.GridEX.EditType.NoEdit
                    .Width = 60
                End With
                With .Columns.Item("sltl11")
                    .Caption = "SLTL"
                    .EditType = Janus.Windows.GridEX.EditType.NoEdit
                    .FormatString = "#,###.##"
                    .TextAlignment = Janus.Windows.GridEX.TextAlignment.Far
                    .Width = 60
                End With
                .Columns.Item("hkk7").Visible = False
                .Columns.Item("slss17").Visible = False
                .Columns.Item("slkk17").Visible = False
                .Columns.Item("sltl17").Visible = False
            Else 'kiem ke giua ki
                .Columns.Item("hkk1").Visible = False
                .Columns.Item("slss11").Visible = False
                .Columns.Item("slkk11").Visible = False
                .Columns.Item("sltl11").Visible = False
                With .Columns.Item("hkk7")
                    .Caption = "HKK"
                    .EditType = Janus.Windows.GridEX.EditType.NoEdit
                    .Width = 30
                End With
                With .Columns.Item("slss17")
                    .Caption = "SLSS"
                    .FormatString = "#,###.##"
                    .TextAlignment = Janus.Windows.GridEX.TextAlignment.Far
                    .EditType = Janus.Windows.GridEX.EditType.NoEdit
                    .Width = 60
                End With
                With .Columns.Item("slkk17")
                    .Caption = "SLKK"
                    .FormatString = "#,###.##"
                    .TextAlignment = Janus.Windows.GridEX.TextAlignment.Far
                    .EditType = Janus.Windows.GridEX.EditType.NoEdit
                    .Width = 60
                End With
                With .Columns.Item("sltl17")
                    .Caption = "SLTL"
                    .FormatString = "#,###.##"
                    .TextAlignment = Janus.Windows.GridEX.TextAlignment.Far
                    .EditType = Janus.Windows.GridEX.EditType.NoEdit
                    .Width = 60
                End With
            End If
        End With
    End Sub
#End Region

#Region "In Bao Cao"
    Private Sub btnIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnIn.Click
        Dim flag As Boolean = False
        Dim strsql = "select * from viewChiTietKiemKe "
        If (m_nhakho <> "") Then
            strsql &= " where kho = '" & m_nhakho & "'"
            If (m_loaikho <> "") Then
                strsql &= " and lk = '" & m_loaikho & "'"
            End If
        Else
            If (m_loaikho <> "") Then
                strsql &= " where lk = '" & m_loaikho & "'"
            Else
                flag = True
                If (m_loaikiemke) Then
                    'dau ki
                    strsql &= " where slss11 <> 0 and slss11 is not NULL"
                Else 'giua ki
                    strsql &= " where slss17 <> 0 and slss17 is not NULL"
                End If
            End If
        End If
        If (Not flag) Then
            If (m_loaikiemke) Then
                'dau ki
                strsql &= " and slss11 <> 0 and slss11 is not NULL"
            Else 'giua ki
                strsql &= " and slss17 <> 0 and slss17 is not NULL"
            End If
        End If
        strsql &= " order by maloaithuoc"
        Dim ds As DataSet
        ds = BaseDB.ExecSql_DataSet(strsql)
        'bien ban kiem ke
        If (MsgBox("Yes:In    No: Excel", MsgBoxStyle.YesNo, "Chú Ý") = MsgBoxResult.Yes) Then
            Dim nnreport As New rptPhieuKiemKe
            nnreport.SetDataSource(ds.Tables(0))
            nnreport.SetParameterValue(0, m_loaikiemke)
            If (m_nhakho <> "") Then
                nnreport.SetParameterValue("nhakho", CDoiTuongs.GetDoiTuongByMa(enumDOITUONG.KHO, m_nhakho).Ten)
            Else
                nnreport.SetParameterValue("nhakho", "")
            End If
            If (m_loaikho <> "") Then
                nnreport.SetParameterValue("loaikho", CDoiTuongs.GetDoiTuongByMa(enumDOITUONG.LOAIKHO, m_loaikho).Ten)
            Else
                nnreport.SetParameterValue("loaikho", "")
            End If
            Dim frm As New frmReportViewer(nnreport)
            frm.ShowDialog()
        Else
            ToEXCELBIENBANKIEMKE(Application.StartupPath & "\tempBIENBANKIEMKE.xls", ds)
        End If
    End Sub

    Private Sub btnBaoCaoKiemKe_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBaoCaoKiemKe.Click
        'bao cao kiem ke
        Dim flag As Boolean = False
        Dim strsql = "select * from viewChiTietKiemKe "
        If (m_nhakho <> "") Then
            strsql &= " where kho = '" & m_nhakho & "' "
            If (m_loaikho <> "") Then
                strsql &= " and lk = '" & m_loaikho & "' "
            End If
        Else
            If (m_loaikho <> "") Then
                strsql &= " where lk = '" & m_loaikho & "' "
            Else
                flag = True
                If (m_loaikiemke) Then
                    'dau ki
                    strsql &= " where slss11 <> 0 and slss11 is not NULL "
                Else 'giua ki
                    strsql &= " where slss17 <> 0 and slss17 is not NULL "
                End If
            End If
        End If

        If (MsgBox("Yes: Thuốc - Hóa Chất - Bông Băng     No: Dụng cụ - Trang Bị", MsgBoxStyle.YesNo, "Chú ý") = MsgBoxResult.Yes) Then
            strsql &= "and (left(ma,3) = 'Y05' or left(ma,3) = 'Y06' or left(ma,3) = 'Y07' or left(ma,3) = 'Y08') "
        Else
            strsql &= "and (left(ma,3) = 'Y01' or left(ma,3) = 'Y02' or left(ma,3) = 'Y03' or left(ma,3) = 'Y04') "
        End If

        If (Not flag) Then
            If (m_loaikiemke) Then
                'dau ki
                strsql &= " and slss11 <> 0 and slss11 is not NULL"
            Else 'giua ki
                strsql &= " and slss17 <> 0 and slss17 is not NULL"
            End If
        End If
        strsql &= " order by maloaithuoc"
        Dim ds As DataSet
        ds = BaseDB.ExecSql_DataSet(strsql)

        If (MsgBox("Yes:In    No: Excel", MsgBoxStyle.YesNo, "Chú Ý") = MsgBoxResult.Yes) Then
            Dim nnreport As New rptBaoCaoKiemKe
            nnreport.SetDataSource(ds.Tables(0))
            nnreport.SetParameterValue(0, m_loaikiemke)
            If (m_loaikho <> "") Then
                nnreport.SetParameterValue(1, CDoiTuongs.GetDoiTuongByMa(enumDOITUONG.LOAIKHO, m_loaikho).Ten)
            Else
                nnreport.SetParameterValue(1, "")
            End If
            Dim frm As New frmReportViewer(nnreport)
            frm.ShowDialog()
        Else
            ToEXCEL(Application.StartupPath & "\tempKiemKe.xls", ds)
        End If
    End Sub

    Private Sub ToEXCEL(ByVal mFileName2 As String, ByVal m_ds As DataSet)
        Dim mFileName As String = Application.StartupPath & "\KiemKe.xls"

        System.IO.File.Copy(mFileName, mFileName2, True)
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim mRange As Excel.Range

        xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
        xlBook = CType(xlApp.Workbooks.Open(mFileName2), Excel.Workbook)

        xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)

        xlSheet.Activate()
        xlSheet.Application.Visible = True

        'dau phieu
        If (m_loaikiemke) Then
            xlSheet.Cells(4, 5) = "Số Lượng" + Chr(10) + "Kiểm Kê Ngày" + Chr(10) + "01/01/" + Now.Year.ToString
        Else
            xlSheet.Cells(4, 5) = "Số Lượng" + Chr(10) + "Kiểm Kê Ngày" + Chr(10) + "01/07/" + Now.Year.ToString
        End If

        Dim iRow0 As Integer = 7
        Dim len, i, j As Integer
        Dim tenloaithuoc As String
        Dim stt_kho As Integer = 0
        Dim gt_sosach, gt_kiemke As Double
        '
        i = 0
        j = 0
        '(i chay trong ds, j chay trong excel)
        len = m_ds.Tables(0).Rows.Count
        While (i < len)
            If (i >= len) Then
                Exit While
            End If
            tenloaithuoc = m_ds.Tables(0).Rows.Item(i).Item("ten_loaithuoc")
            xlSheet.Cells(iRow0 + j, 1) = "      " + tenloaithuoc
            mRange = xlSheet.Cells(iRow0 + j, 1)
            mRange.Font.Bold = True
            j += 1
            While (m_ds.Tables(0).Rows.Item(i).Item("ten_loaithuoc") = tenloaithuoc)
                xlSheet.Cells(iRow0 + j, 1) = m_ds.Tables(0).Rows.Item(i).Item("ten") + Chr(10) + "   " + ChuanHoaChuoi(m_ds.Tables(0).Rows.Item(i).Item("lo"))
                xlSheet.Cells(iRow0 + j, 2) = m_ds.Tables(0).Rows.Item(i).Item("dvt")
                xlSheet.Cells(iRow0 + j, 3) = m_ds.Tables(0).Rows.Item(i).Item("gle")
                xlSheet.Cells(iRow0 + j, 4) = ChuanHoaChuoi(m_ds.Tables(0).Rows.Item(i).Item("h"))
                If (m_loaikiemke) Then 'thang 1
                    xlSheet.Cells(iRow0 + j, 5) = m_ds.Tables(0).Rows.Item(i).Item("slkk11t")
                    '
                    xlSheet.Cells(iRow0 + j, 8) = m_ds.Tables(0).Rows.Item(i).Item("slss11")
                    xlSheet.Cells(iRow0 + j, 9) = m_ds.Tables(0).Rows.Item(i).Item("slkk11")
                    xlSheet.Cells(iRow0 + j, 10) = m_ds.Tables(0).Rows.Item(i).Item("slkk11") * m_ds.Tables(0).Rows.Item(i).Item("gle")
                    If (m_ds.Tables(0).Rows.Item(i).Item("slkk11") > m_ds.Tables(0).Rows.Item(i).Item("slss11")) Then
                        xlSheet.Cells(iRow0 + j, 11) = m_ds.Tables(0).Rows.Item(i).Item("slkk11") - m_ds.Tables(0).Rows.Item(i).Item("slss11")
                    ElseIf (m_ds.Tables(0).Rows.Item(i).Item("slkk11") < m_ds.Tables(0).Rows.Item(i).Item("slss11")) Then
                        xlSheet.Cells(iRow0 + j, 12) = -(m_ds.Tables(0).Rows.Item(i).Item("slkk11") - m_ds.Tables(0).Rows.Item(i).Item("slss11"))
                    Else 'bang nhau ko in ra
                    End If
                    xlSheet.Cells(iRow0 + j, 13) = "*"
                    xlSheet.Cells(iRow0 + j, 14) = m_ds.Tables(0).Rows.Item(i).Item("sltl11")
                    gt_sosach += m_ds.Tables(0).Rows.Item(i).Item("slss11") * m_ds.Tables(0).Rows.Item(i).Item("gle")
                    gt_kiemke += m_ds.Tables(0).Rows.Item(i).Item("slkk11") * m_ds.Tables(0).Rows.Item(i).Item("gle")
                Else 'thang 7
                    xlSheet.Cells(iRow0 + j, 5) = m_ds.Tables(0).Rows.Item(i).Item("slkk17t")
                    'chu y khac
                    xlSheet.Cells(iRow0 + j, 6) = m_ds.Tables(0).Rows.Item(i).Item("sln17t") + m_ds.Tables(0).Rows.Item(i).Item("sln17")
                    xlSheet.Cells(iRow0 + j, 7) = m_ds.Tables(0).Rows.Item(i).Item("slx17t") + m_ds.Tables(0).Rows.Item(i).Item("slx17")
                    '
                    xlSheet.Cells(iRow0 + j, 8) = m_ds.Tables(0).Rows.Item(i).Item("slss17")
                    xlSheet.Cells(iRow0 + j, 9) = m_ds.Tables(0).Rows.Item(i).Item("slkk17")
                    xlSheet.Cells(iRow0 + j, 10) = m_ds.Tables(0).Rows.Item(i).Item("slkk17") * m_ds.Tables(0).Rows.Item(i).Item("gle")
                    If (m_ds.Tables(0).Rows.Item(i).Item("slkk17") > m_ds.Tables(0).Rows.Item(i).Item("slss17")) Then
                        xlSheet.Cells(iRow0 + j, 11) = m_ds.Tables(0).Rows.Item(i).Item("slkk17") - m_ds.Tables(0).Rows.Item(i).Item("slss17")
                    ElseIf (m_ds.Tables(0).Rows.Item(i).Item("slkk17") < m_ds.Tables(0).Rows.Item(i).Item("slss17")) Then
                        xlSheet.Cells(iRow0 + j, 12) = -(m_ds.Tables(0).Rows.Item(i).Item("slkk17") - m_ds.Tables(0).Rows.Item(i).Item("slss17"))
                    Else 'bang nhau ko in ra
                    End If
                    xlSheet.Cells(iRow0 + j, 13) = "*"
                    xlSheet.Cells(iRow0 + j, 14) = m_ds.Tables(0).Rows.Item(i).Item("sltl17")
                    gt_sosach += m_ds.Tables(0).Rows.Item(i).Item("slss17") * m_ds.Tables(0).Rows.Item(i).Item("gle")
                    gt_kiemke += m_ds.Tables(0).Rows.Item(i).Item("slkk17") * m_ds.Tables(0).Rows.Item(i).Item("gle")
                End If
                i += 1
                j += 1
                stt_kho += 1
                If (i >= len) Then
                    Exit While
                End If
            End While
            '
        End While

        xlApp.ActiveWindow.Visible = False
        xlApp.Windows(1).Activate()
        '
        'duoi phieu
        xlSheet.Cells(iRow0 + j + 2, 2) = "Tổng cộng Kho có: " + CStr(stt_kho) + " mặt hàng"
        xlSheet.Cells(iRow0 + j + 3, 2) = "Giá trị theo sổ sách là:  " + CStr(gt_sosach) + "  đồng"
        xlSheet.Cells(iRow0 + j + 4, 2) = "Giá trị theo kiểm kê là:  " + CStr(gt_kiemke) + "  đồng" + "                              Chênh lệch kiểm kê và sổ sách là :  " + CStr(gt_kiemke - gt_sosach) + "  đồng"
        xlSheet.Cells(iRow0 + j + 6, 2) = "              In tại Phòng Vi tính Kho 706"
        xlSheet.Cells(iRow0 + j + 7, 4) = "                     TRƯỞNG BAN KẾ HOẠCH" + "                                                 " + "TRƯỞNG BAN TÀI CHÍNH" + "                                                 " + "CHỈ HUY KHO"
        mRange = xlSheet.Cells(iRow0 + j + 7, 4)
        mRange.Font.Bold = True
    End Sub

    Private Sub ToEXCELBIENBANKIEMKE(ByVal mFileName2 As String, ByVal m_ds As DataSet)
        Dim mFileName As String = Application.StartupPath & "\BIENBANKIEMKE.xls"

        System.IO.File.Copy(mFileName, mFileName2, True)
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim mRange As Excel.Range

        xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
        xlBook = CType(xlApp.Workbooks.Open(mFileName2), Excel.Workbook)

        xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)

        xlSheet.Activate()
        xlSheet.Application.Visible = True

        'dau phieu
        'c3
        xlSheet.Cells(3, 3) = "Đã kiểm kê TB-KT-VT của kho hàng : " + m_loaikho + " Nhà kho : " + m_nhakho + " Từ    giờ, Ngày            đến ngày "

        Dim iRow0 As Integer = 7
        Dim len, i, j As Integer
        Dim maloaithuoc As String
        Dim stt_kho As Integer = 0
        '
        i = 0
        j = 0
        '(i chay trong ds, j chay trong excel)
        len = m_ds.Tables(0).Rows.Count
        While (i < len)
            If (i >= len) Then
                Exit While
            End If
            maloaithuoc = m_ds.Tables(0).Rows.Item(i).Item("maloaithuoc")
            xlSheet.Cells(iRow0 + j, 1) = "      " + maloaithuoc
            mRange = xlSheet.Cells(iRow0 + j, 1)
            mRange.Font.Bold = True
            j += 1
            Dim stt As Integer = 0
            While (m_ds.Tables(0).Rows.Item(i).Item("maloaithuoc") = maloaithuoc)
                xlSheet.Cells(iRow0 + j, 1) = m_ds.Tables(0).Rows.Item(i).Item("ma")
                xlSheet.Cells(iRow0 + j, 2) = m_ds.Tables(0).Rows.Item(i).Item("ten")
                xlSheet.Cells(iRow0 + j, 3) = ChuanHoaChuoi(m_ds.Tables(0).Rows.Item(i).Item("lo"))
                xlSheet.Cells(iRow0 + j, 4) = ChuanHoaChuoi(m_ds.Tables(0).Rows.Item(i).Item("hansd"))
                xlSheet.Cells(iRow0 + j, 5) = ChuanHoaChuoi(m_ds.Tables(0).Rows.Item(i).Item("dvt"))
                xlSheet.Cells(iRow0 + j, 6) = ChuanHoaChuoi(m_ds.Tables(0).Rows.Item(i).Item("h"))
                If (m_loaikiemke) Then 'thang1
                    xlSheet.Cells(iRow0 + j, 7) = m_ds.Tables(0).Rows.Item(i).Item("slss11")
                Else 'thang 7
                    xlSheet.Cells(iRow0 + j, 7) = m_ds.Tables(0).Rows.Item(i).Item("slss17")
                End If
                xlSheet.Cells(iRow0 + j, 11) = m_ds.Tables(0).Rows.Item(i).Item("gle")
                i += 1
                j += 1
                stt += 1
                stt_kho += 1
                If (i >= len) Then
                    Exit While
                End If
            End While
            xlSheet.Cells(iRow0 + j, 2) = "Tổng cộng nhóm có: " + CStr(stt) + " mặt hàng"
            j += 1
            '
        End While
        xlSheet.Cells(iRow0 + j, 2) = "Tổng cộng Kho có: " + CStr(stt_kho) + " mặt hàng"


        xlApp.ActiveWindow.Visible = False
        xlApp.Windows(1).Activate()
        '
        'duoi phieu

        xlSheet.Cells(iRow0 + j + 2, 1) = "                     Người Quản Lý TB-KT_VT" + "                                                 " + "Thành Viên Tổ Kiểm Kê" + "                                                 " + "Tổ Trưởng Kiểm Kê"
        mRange = xlSheet.Cells(iRow0 + j + 2, 1)
        mRange.Font.Bold = True
    End Sub
#End Region

    
End Class

'co van de ngay tai day can duoc xu ly

#Region "dtgGroup"

Public Class DataRowComparer
    Implements System.Collections.IComparer
    Private direction As ListSortDirection
    Private columnIndex As Integer

    Public Sub New(ByVal col As Integer, ByVal di As ListSortDirection)
        columnIndex = col
        direction = di
    End Sub

    Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer Implements System.Collections.IComparer.Compare
        Dim obj1 As DataRow = CType(x, DataRow)
        Dim obj2 As DataRow = CType(y, DataRow)
        Dim kq As Integer
        If (direction = ListSortDirection.Ascending) Then
            kq = 1
        Else
            kq = -1
        End If

        'so sanh kieu thoi gian
        'If (obj1(columnIndex).GetType.ToString = "System.DateTime") Then
        '    If (obj1(columnIndex) > obj2(columnIndex)) Then
        '        Return kq
        '    Else
        '        Return -kq
        '    End If
        'End If

        'con lai la so sanh chuoi
        Return String.Compare(obj1(columnIndex).ToString(), obj2(columnIndex).ToString()) * kq
    End Function

    'Public Function SoSanhNgay(ByVal ngay) As Integer

    'End Function

    Public Sub DataRowComparer(ByVal columnIndex As Integer, ByVal direction As ListSortDirection)
        Me.columnIndex = columnIndex
        Me.direction = direction
    End Sub

End Class

'Public Class ContactInfoComparer
'xay dung lop so sanh theo doi tuong la contactInfo
'    Implements System.Collections.IComparer
'    Private propertyIndex As Integer
'    Private direction As ListSortDirection

'    Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer Implements System.Collections.IComparer.Compare
'        'Dim obj1 As Contaner = CType(x, Container)
'        'Dim obj2 As Container = CType(y, Container)

'        'Select Case (propertyIndex)
'        '    Case 1
'        '        Return CompareStrings(obj1.Name, obj2.Name)
'        '    Case 2
'        '        Return CompareDates(obj1.Date, obj2.Date)
'        '    Case 3
'        '        Return CompareStrings(obj1.Subject, obj2.Subject)
'        '    Case 4
'        '        Return CompareNumbers(obj1.Concentration, obj2.Concentration)
'        '    Case Else
'        '            return CompareNumbers((double)obj1.Id, (double)obj2.Id)
'        'End Select
'    End Function

'    Private Function CompareStrings(ByVal val1 As String, ByVal val2 As String) As Integer
'        Dim kq As Integer
'        If (direction = ListSortDirection.Ascending) Then
'            kq = 1
'        Else
'            kq = -1
'        End If
'        Return String.Compare(val1, val2) * kq
'    End Function

'    Private Function CompareDates(ByVal val1 As Date, ByVal val2 As Date) As Integer
'        Dim kq As Integer
'        If (direction = ListSortDirection.Ascending) Then
'            kq = 1
'        Else
'            kq = -1
'        End If
'        If (val1 > val2) Then
'            Return kq
'        End If
'        If (val1 < val2) Then
'            Return -kq
'        End If
'        Return 0
'    End Function

'    Private Function CompareNumbers(ByVal val1 As Double, ByVal val2 As Double) As Integer
'        Dim kq As Integer
'        If (direction = ListSortDirection.Ascending) Then
'            kq = 1
'        Else
'            kq = -1
'        End If
'        If (val1 > val2) Then
'            Return kq
'        End If
'        If (val1 < val2) Then
'            Return -kq
'        End If
'        Return 0
'    End Function

'    Public Sub ContactInfoComparer(ByVal propertyIndex As Integer, ByVal direction As ListSortDirection)
'        Me.propertyIndex = propertyIndex
'        Me.direction = direction
'    End Sub
'End Class
#End Region




